package com.example.sql_valid.service;

import com.example.sql_valid.controller.SQLValidatorController;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
public class SQLValidatorService {


    private static final Logger logger = LoggerFactory.getLogger(SQLValidatorController.class);
    /**
     * 验证SQL文件schema
     *
     * @param files
     * @param schema
     * @param checkTablespace
     * @return
     */
    public String SchemaValid(List<MultipartFile> files, String schema, boolean checkTablespace) {
        StringBuilder result = new StringBuilder();
        Pattern schemaViewTablePattern = Pattern.compile("\\b(view|from|table|into|update|merge into)\\s+([^\\s\\(\\);]+)", Pattern.CASE_INSENSITIVE);
        Pattern createTablePattern = Pattern.compile("\\bcreate\\s+table\\b", Pattern.CASE_INSENSITIVE);
        Pattern createIndexPattern = Pattern.compile("\\bcreate\\s+(unique\\s+)?index\\b", Pattern.CASE_INSENSITIVE);
        Pattern tablespacePattern = Pattern.compile("\\btablespace\\b", Pattern.CASE_INSENSITIVE);

        for (MultipartFile file : files) {
            String fileName = file.getOriginalFilename();
            StringBuilder fileErrors = new StringBuilder();
            try {
                List<String> lines = readLines(file);
                String rawSQL = String.join("\n", lines);
                String cleanSQL = removeComments(rawSQL);
                String[] sqlLines = cleanSQL.split("\n");
                for (int i = 0; i < sqlLines.length; i++) {
                    String line = sqlLines[i];
                    Matcher matcher = schemaViewTablePattern.matcher(line);

                    boolean schemaMismatch = false;
                    while (matcher.find()) {
                        String schemaName = matcher.group(2).split("\\.")[0].replace("\"", "");
                        if (!schemaName.equalsIgnoreCase(schema)) {
                            schemaMismatch = true;
                            break;
                        }
                    }

                    if (schemaMismatch) {
                        fileErrors.append("文件: ").append(fileName).append(" - Schema 错误或缺失，位于第 ").append(i + 1).append(" 行\n");
                    } else if (checkTablespace && (createTablePattern.matcher(line).find() || createIndexPattern.matcher(line).find()) && !tablespacePattern.matcher(line).find()) {
                        fileErrors.append("文件: ").append(fileName).append(" - 缺少表空间语句，位于第 ").append(i + 1).append(" 行\n");
                    }
                }

                if (fileErrors.length() == 0) {
                    result.append("文件: ").append(fileName).append(" - 验证通过\n");
                }

            } catch (IOException e) {
                fileErrors.append("文件: ").append(fileName).append(" - 读取失败\n");
            }

            if (fileErrors.length() > 0) {
                result.append(fileErrors);
                try {
                    Path errorFilePath = Paths.get(System.getProperty("user.dir"), fileName.replace(".sql", "_error.sql"));
                    Files.write(errorFilePath, fileErrors.toString().getBytes(Charset.forName("UTF-8")));
                    result.append("错误SQL已保存到: ").append(errorFilePath.toAbsolutePath()).append("\n");
                    logger.info("错误SQL已保存到: " + errorFilePath.toAbsolutePath());
                } catch (IOException e) {
                    result.append("保存错误文件时出错: ").append(e.getMessage()).append("\n");
                    logger.error("保存错误文件时出错: " + e.getMessage(), e);
                }
            }


        }

        return result.toString();

    }

    private List<String> readLines(MultipartFile file) throws IOException {
        Charset encoding = Charset.forName("UTF-8");
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(file.getInputStream(), encoding))) {
            return reader.lines().collect(Collectors.toList());
        }

    }

    private String removeComments(String sql) {
        sql = sql.replaceAll("--.*?$", "");
        sql = sql.replaceAll("/\\*.*?\\*/", "");
        return sql;
    }

}
